
--1 基础参数

CREATE TABLE [dbo].[BASE_PARAM]
(
  [ID]         [int] IDENTITY (1, 1)    NOT NULL,
  [NAME]       [nvarchar](50)             NOT NULL,
  [POS]        [int]                      NOT NULL,
  [CATEGORY]   [nvarchar](50)             NOT NULL,
  [SERVERFILE] [nvarchar](200),
  [FILEEXT]    [nvarchar](100),
  [FILESIZE]     [int],
  [STATUS]       [int],
  [CREATE_ID]    [int],
  [CREATE_TIME]  [datetime],
  [UPDATE_ID]    [int],
  [UPDATE_TIME]  [datetime],
  [DESCN]        [nvarchar](255)
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[BASE_PARAM] ADD 
	 PRIMARY KEY  CLUSTERED ([id]) ON [PRIMARY] 
GO


--2 参数类型

CREATE TABLE [dbo].[INSPECT_TYPE]
(
  [ID]           [int]   IDENTITY (1, 1)                        NOT NULL,
  [NAME]         [nvarchar](50)                NOT NULL,
  [POS]          [int]                          NOT NULL,
  [STATUS]       [int],
  [CREATE_ID]    [int],
  [CREATE_TIME]  [datetime],
  [UPDATE_ID]    [int],
  [UPDATE_TIME]  [datetime],
  [DESCN]        nvarchar(200)
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_TYPE] ADD 
	 PRIMARY KEY  CLUSTERED ([id])ON [PRIMARY] 
GO

--3 督办观察员

CREATE TABLE [dbo].[INSPECT_OBSERVER]
(
  [ID]           [int]   IDENTITY (1, 1)                    NOT NULL,
  [CREATE_ID]    [int],
  [CREATE_TIME]  [datetime],
  [UPDATE_ID]    [int],
  [UPDATE_TIME]  [datetime],
  [USER_ID]      [int]
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_OBSERVER] ADD 
     PRIMARY KEY  CLUSTERED ([id])  ON [PRIMARY], 
     CONSTRAINT [FK5A0B38A1E56636FD] 
        FOREIGN KEY ([USER_ID]) REFERENCES [dbo].[SYSTEM_USERS]([id])
GO


--4 督办观察员有权查看的部门

CREATE TABLE [dbo].[INSPECT_OBSERVER_DEPT]
(
  [ID]                   [int]  IDENTITY (1, 1)             NOT NULL,
  [DEPT_ID]              [int],
  [INSPECT_OBSERVER_ID]  int              NOT NULL,
  [INSPECTOBSERVER_ID]   [int]
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_OBSERVER_DEPT] ADD 
      PRIMARY KEY  CLUSTERED ([id])  ON [PRIMARY],
      CONSTRAINT [FKDC5DBB233C8CB251] 
                 FOREIGN KEY ([INSPECT_OBSERVER_ID]) 
                 REFERENCES [dbo].[INSPECT_OBSERVER]([ID]),
      CONSTRAINT [FKDC5DBB234ACC6CBD] 
                 FOREIGN KEY ([DEPT_ID]) 
                 REFERENCES [dbo].[SYSTEM_DEPTS]([ID]),
      CONSTRAINT [FKDC5DBB23873DB968] 
                 FOREIGN KEY ([INSPECTOBSERVER_ID]) 
                 REFERENCES [dbo].[INSPECT_OBSERVER] ([ID])
GO
--5 督办编号设置

CREATE TABLE [dbo].[INSPECT_SN_SET]
(
  [ID]         [int]     IDENTITY (1, 1)  NOT NULL,
  [CREATE_ID]    [int],
  [CREATE_TIME]  [datetime],
  [UPDATE_ID]    [int],
  [UPDATE_TIME]  [datetime],
  [SN_RULE]         nvarchar(1),
  [CONDITION]    nvarchar(60),
  [FORMAT]       nvarchar(100),
  [BEGIN_NO]     [int],
  [DIGIT]        nvarchar(10),
  [CAN_BREAK]    [int],
  [START_DATE]   [datetime],
  [END_DATE]     [datetime]
)ON [PRIMARY]

GO

ALTER TABLE INSPECT_SN_SET ADD 
      PRIMARY KEY  CLUSTERED([ID])on [PRIMARY]

GO
--6 断号记录

CREATE TABLE [dbo].[INSPECT_SN_REMAIN]
(
  [ID]               int   IDENTITY (1, 1)                NOT NULL,
  [CREATE_ID]        [int],
  [CREATE_TIME]      [datetime],
  [UPDATE_ID]        [int],
  [UPDATE_TIME]      [datetime],
  [INSPECT_SN]       nvarchar(100),
  [INSPECT_FILE_ID]  [int],
  [SN_INDEX]         [nvarchar](100),
  [SN_VALUE]         nvarchar(100),
  [SN_FROMAT]        nvarchar(250),
  [IS_BREAK_SN]      [int]
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_SN_REMAIN] ADD 
     PRIMARY KEY  CLUSTERED([ID])on [PRIMARY]

GO

--7 督办最新值

CREATE TABLE [dbo].[INSPECT_FILE_SN]
(
  [ID]           [int]       IDENTITY (1, 1)                NOT NULL,
  
  [CREATE_ID]    [int],
  [CREATE_TIME]  [datetime],
  [UPDATE_ID]   [int],
  [UPDATE_TIME]  [datetime],
  [SN_INDEX]     [nvarchar](100),
  [SN_VALUE]     [nvarchar](100),
  [INSPECT_SN_ID] [int],
  [SN_FORMAT]     [nvarchar](100),
  [SN_FORMAT_VALUE]     [nvarchar](100),
  [SN_LAST]      [int]
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_FILE_SN] ADD 
       PRIMARY KEY  CLUSTERED([ID]) ON [PRIMARY]

--8 督办信息

CREATE TABLE [dbo].[INSPECT_FILE]
(
  [ID]               int     IDENTITY (1, 1)              NOT NULL,
  [SN]               nvarchar(100),
  [NAME]             nvarchar(200)               NOT NULL,
  [INSPECT_DATE]     [datetime],
  [LINKMAN]          nvarchar(50),
  [PHONE]            nvarchar(50),
  [MAIN]             nvarchar(50),
  [ASSIST]           nvarchar(50),
  [CONTENT]          nvarchar(2000),
  [STATUS]           [int],
  [CREATE_ID]        [int],
  [CREATE_TIME]      [datetime],
  [UPDATE_ID]        [int],
  [UPDATE_TIME]      [datetime],
  [SEND_DATE]      [datetime],
  [INSPECT_TYPE_ID]  int                  NOT NULL,
  [PRIORITY_ID]      [int],
  [INSPECT_DEPT_ID]  [int],
  [AUDIT_USER_ID]    [int],
  [USER_ID]          [int],
  [SN_FORMAT]        nvarchar(100),
  [OPTINION]         nvarchar(1000),
  [OLD_STATUS]       [int]
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_FILE] ADD 
      PRIMARY KEY  CLUSTERED( [ID] )on [PRIMARY],
      CONSTRAINT [FKF94C78C7A7DF2192]
            FOREIGN KEY ([INSPECT_DEPT_ID]) 
            REFERENCES [dbo].[SYSTEM_DEPTS]([ID]),
      CONSTRAINT [FKF94C78C7E56636FD]
            FOREIGN KEY ( [USER_ID] ) 
            REFERENCES [dbo].[SYSTEM_USERS]( [ID] ),
      CONSTRAINT [FKF94C78C7E0436E99]
            FOREIGN KEY ([AUDIT_USER_ID]) 
            REFERENCES [dbo].[SYSTEM_USERS]([ID]),
     CONSTRAINT [FKF94C78C77CE29820]
            FOREIGN KEY ([PRIORITY_ID] ) 
            REFERENCES [dbo].[BASE_PARAM]([ID] ),
     CONSTRAINT [FKF94C78C711C06B51]
            FOREIGN KEY ([INSPECT_TYPE_ID]) 
            REFERENCES [dbo].[INSPECT_TYPE]([ID])
GO

--9.督办关联附件

CREATE TABLE [dbo].[INSPECT_FILE_ATTACH]
(
  [ID]               int      IDENTITY (1, 1)             NOT NULL,
  [USER_ID]          [int],
  [CREATEDATE]       [datetime],
  [SERVERFILE]       nvarchar(200)               NOT NULL,
  [FILENAME]         nvarchar(100)               NOT NULL,
  [FILEEXT]          nvarchar(100)               NOT NULL,
  [FILESIZE]         [int],
  [INSPECT_FILE_ID]  int                  NOT NULL
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_FILE_ATTACH] ADD 
         PRIMARY KEY  CLUSTERED( [ID] )on [PRIMARY],
         CONSTRAINT [FKD3D554BD119CAC11]
               FOREIGN KEY ([INSPECT_FILE_ID]) 
               REFERENCES [dbo].[INSPECT_FILE]([ID])
GO
--10.事项信息

CREATE TABLE [dbo].[INSPECT_PROCEED]
(
  [ID]                 int     IDENTITY (1, 1)            NOT NULL,
  [TITLE]              nvarchar(200)             NOT NULL,
  [SN]                nvarchar(50)              NOT NULL,
  [ASSIST]             nvarchar(100),
  [STATUS]             int                NOT NULL,
  [REMIND_COUNT]       [int],
  [DECIDE_TIME]      [datetime],
  [NEED_FINISH_TIME]   [datetime],
  [WARN_HOUR]          [int],
  [IS_REMIND]          [int],
  [REMIND_DATE]        [datetime],
  [REMIND_MON]         nvarchar(10),
  [REMIND_DAY]         nvarchar(10),
  [REMIND_HOUR]        nvarchar(10),
  [REMIND_MIN]         nvarchar(10),
  [REMIND_MODE]        [int],
  [IS_CYCLE_REMIND]    [int],
  [CYCLE_REMIND_MODE]  [int],
  [CYCLE_SETTING]      nvarchar(20),
  [DEFER_DATE]         [datetime],
  [CREATE_ID]          [int],
  [CREATE_TIME]        [datetime],
  [UPDATE_ID]          [int],
  [UPDATE_TIME]        [datetime],
  [SEND_DATE]        [datetime],
  [INSPECT_FILE_ID]    int                NOT NULL,
  [CYCLE_END_DATE]     [datetime],
  [PROGRESS]         nvarchar(10),
  [OPINION]            nvarchar(1000),
  [OLD_STATUS]         [int],
  [IS_DEFER]           [int],
  [FINISH_TIME]        [datetime],
  [DEFER_DESCN]        nvarchar(400)
)ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_PROCEED] ADD 
  PRIMARY KEY CLUSTERED (ID)on [PRIMARY],
  CONSTRAINT [FK4A9FD183119CAC11]
       FOREIGN KEY ([INSPECT_FILE_ID]) 
       REFERENCES [dbo].[INSPECT_FILE] ([ID])


--11.督办参与人员

CREATE TABLE [dbo].[INSPECT_USER]
(
  [ID]                  int   IDENTITY (1, 1)             NOT NULL,
  [TYPE]                nvarchar(50),
  [STATUS]              [int],
  [CREATE_ID]           [int],
  [CREATE_TIME]         [datetime],
  [UPDATE_ID]           [int],
  [UPDATE_TIME]         [datetime],
  [WEIGHT]              [int],
  [PROGRESS]            [int],
  [INSPECT_PROCEED_ID]  [int],
  [INSPECT_FILE_ID]     [int],
  [USER_ID]             [int],
  [NONE]                [int],
  [ELT]                 [int],
  [PARENT_ID]           [int],
  [DEFER_COUNT]         [int],
  [DEFER_INFO]          nvarchar(2000),
  [PRESENT_ID]          [int]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[INSPECT_USER] ADD
  PRIMARY KEY CLUSTERED (ID)on [PRIMARY],
  CONSTRAINT FKF9536F16E56636FD 
           FOREIGN KEY (USER_ID) REFERENCES [dbo].[SYSTEM_USERS] (ID),
  CONSTRAINT FKF9536F16119CAC11 
           FOREIGN KEY (INSPECT_FILE_ID) REFERENCES [dbo].[INSPECT_FILE] (ID),
  CONSTRAINT FKF9536F1640C3EBC3 
           FOREIGN KEY (INSPECT_PROCEED_ID)REFERENCES [dbo].[INSPECT_PROCEED] (ID),
  CONSTRAINT FKF9536F1662EDE39D 
           FOREIGN KEY (PARENT_ID) REFERENCES [dbo].[INSPECT_USER] (ID),
  CONSTRAINT FKF9536F16EE2CD19B 
           FOREIGN KEY (ELT) REFERENCES [dbo].[SYSTEM_USERS] (ID),
  CONSTRAINT FKF9536F16EE5EF826 
           FOREIGN KEY (NONE)REFERENCES [dbo].[SYSTEM_USERS] (ID),
  CONSTRAINT FKF9536F1640032DEC 
           FOREIGN KEY (PRESENT_ID) REFERENCES [dbo].[INSPECT_USER] (ID)
GO

--12.督办审核信息

CREATE TABLE [dbo].[INSPECT_AUDIT_INFO]
(
  [ID]                int        IDENTITY (1, 1)          NOT NULL,
  [STATUS]            nvarchar(50)               NOT NULL,
  [CREATE_TIME]       [datetime]                        NOT NULL,
  [DESCN]             nvarchar(1000),
  [USER_ID]           [int],
  [WORKFLOW_SIGN_ID]  [int],
  [INSPECT_FILE_ID]   [int],
  [INSPECT_USER_ID]   [int]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_AUDIT_INFO] ADD 
     PRIMARY KEY CLUSTERED (ID)on [PRIMARY],
     CONSTRAINT FK68BE519D3BD4D5B1 
             FOREIGN KEY (INSPECT_USER_ID)REFERENCES [dbo].[INSPECT_USER] (ID),
     CONSTRAINT FK68BE519D119CAC11 
             FOREIGN KEY (INSPECT_FILE_ID) REFERENCES [dbo].[INSPECT_FILE] (ID),
     CONSTRAINT FK68BE519DE56636FD 
             FOREIGN KEY (USER_ID) REFERENCES [dbo].[SYSTEM_USERS] (ID),
     CONSTRAINT FK68BE519DDE6A4DDD 
             FOREIGN KEY (WORKFLOW_SIGN_ID)REFERENCES [dbo].[WORKFLOW_SIGNATURE] (ID)
GO

--13 催办信息表

CREATE TABLE [dbo].[INSPECT_REMIND]
(
  [ID]                  int     IDENTITY (1, 1)           NOT NULL,
  [REMIND_TYPE]         [int],
  [STATUS]              [int],
  [REMIND_COUNT]        [int],
  [DESCN]               nvarchar(255),
  [CREATE_ID]           [int],
  [CREATE_TIME]         [datetime],
  [UPDATE_ID]           [int],
  [UPDATE_TIME]         [datetime],
  [USER_ID]             [int],
  [INSPECT_PROCEED_ID]  [int]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[INSPECT_REMIND] ADD 
  PRIMARY KEY CLUSTERED (ID)on [PRIMARY],
  CONSTRAINT FKEC53BD9040C3EBC3 
            FOREIGN KEY ([INSPECT_PROCEED_ID]) REFERENCES [dbo].[INSPECT_PROCEED] ([ID]),
  CONSTRAINT FKEC53BD90E56636FD 
            FOREIGN KEY ([USER_ID])REFERENCES [dbo].[SYSTEM_USERS] ([ID])
GO


 --14 催办接受人信息及状态
 
CREATE TABLE [dbo].[INSPECT_REMIND_USER]
(
  [ID]                 int     IDENTITY (1, 1)            NOT NULL,
  [READ_STATUS]        [int],
  [STATUS]             [int],
  [READ_TIME]          [datetime],
  [USER_ID]            [int],
  [INSPECT_REMIND_ID]  [int]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[INSPECT_REMIND_USER] ADD
      PRIMARY KEY CLUSTERED (ID)on [PRIMARY],  
      CONSTRAINT FK4EA5E11AE56636FD 
                FOREIGN KEY (USER_ID) REFERENCES [dbo].[SYSTEM_USERS] (ID),
      CONSTRAINT FK4EA5E11AC19F79B1 
                FOREIGN KEY (INSPECT_REMIND_ID) REFERENCES INSPECT_REMIND (ID)

GO
--15事项办理信息

CREATE TABLE INSPECT_PROCEED_INFO
(
  [ID]                  int    IDENTITY (1, 1)            NOT NULL,
  [TYPE]                nvarchar(10),
  [DEFER_DATE]          [datetime],
  [CREATE_ID]           [int],
  [CREATE_TIME]         [datetime],
  [UPDATE_ID]           [int],
  [UPDATE_TIME]         [datetime],
  [DESCN]               nvarchar(2000),
  [WORKFLOW_SIGN_ID]    [int],
  [INSPECT_PROCEED_ID]  [int],
  [USER_ID]             [int],
  [PROGRESS]            [int],
  [INSPECT_USER_ID]     [int]
) ON [PRIMARY]
GO


ALTER TABLE INSPECT_PROCEED_INFO ADD 
      PRIMARY KEY CLUSTERED (ID)on [PRIMARY],  
      CONSTRAINT FKA91C5EA40C3EBC3 
             FOREIGN KEY (INSPECT_PROCEED_ID) REFERENCES INSPECT_PROCEED (ID),
      CONSTRAINT FKA91C5EADE6A4DDD 
             FOREIGN KEY (WORKFLOW_SIGN_ID) REFERENCES WORKFLOW_SIGNATURE (ID),
      CONSTRAINT FKA91C5EAE56636FD 
             FOREIGN KEY (USER_ID)REFERENCES [dbo].[SYSTEM_USERS] (ID),
      CONSTRAINT FKA91C5EA3BD4D5B1 
             FOREIGN KEY (INSPECT_USER_ID)REFERENCES INSPECT_USER (ID)

GO
--16 事项附件及办理过程中上传的附件

CREATE TABLE INSPECT_PROCEED_ATTACH
(
  [ID]                       int     IDENTITY (1, 1)      NOT NULL,
  [USER_ID]                  [int],
  [CREATEDATE]               [datetime],
  [SERVERFILE]               nvarchar(200)       NOT NULL,
  [FILENAME]                 nvarchar(100)       NOT NULL,
  [FILEEXT]                  nvarchar(100)       NOT NULL,
  [FILESIZE]                 [int],
  [INSPECT_PROCEED_ID]       int          NOT NULL,
  [INSPECT_PROCEED_INFO_ID]  int          NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE INSPECT_PROCEED_ATTACH ADD 
      PRIMARY KEY CLUSTERED (ID)on [PRIMARY],
      CONSTRAINT FK9FEBF0816F020CCA 
             FOREIGN KEY (INSPECT_PROCEED_INFO_ID) REFERENCES INSPECT_PROCEED_INFO (ID),
      CONSTRAINT FK9FEBF08140C3EBC3 
             FOREIGN KEY (INSPECT_PROCEED_ID) REFERENCES INSPECT_PROCEED (ID)

GO
--17 事项中操作日志

CREATE TABLE INSPECT_LOG
(
  [ID]                  int   IDENTITY (1, 1)             NOT NULL,
  [LOG_TYPE]            nvarchar(50),
  [DESCN]               nvarchar(2000),
  [CREATE_ID]           [int],
  [CREATE_TIME]         [datetime],
  [UPDATE_ID]           [int],
  [UPDATE_TIME]         [datetime],
  [OPER_USER_ID]        [int],
  [INSPECT_PROCEED_ID]  [int],
  [INSPECT_FILE_ID]     [int],
  [INSPECT_PROCEED]     [int],
  [INSPECT_FILE]        [int],
  [OPER_USER]           [int]
) ON [PRIMARY]
GO

ALTER TABLE INSPECT_LOG ADD 
       
      PRIMARY KEY CLUSTERED (ID)on [PRIMARY],  
      CONSTRAINT FKA4F20A99119CAC11 
              FOREIGN KEY (INSPECT_FILE_ID) REFERENCES [dbo].[INSPECT_FILE] (ID),
      CONSTRAINT FKA4F20A9940C3EBC3 
              FOREIGN KEY (INSPECT_PROCEED_ID) REFERENCES [dbo].[INSPECT_PROCEED] (ID),
      CONSTRAINT FKA4F20A99537DD60C 
              FOREIGN KEY (OPER_USER_ID) REFERENCES [dbo].[SYSTEM_USERS] (ID),
      CONSTRAINT FKA4F20A996F242FEF 
              FOREIGN KEY (INSPECT_PROCEED) REFERENCES INSPECT_PROCEED (ID),
      CONSTRAINT FKA4F20A99E0CCB545 
              FOREIGN KEY (INSPECT_FILE)  REFERENCES [dbo].[INSPECT_FILE] (ID),
      CONSTRAINT FKA4F20A99512A880A 
              FOREIGN KEY (OPER_USER) REFERENCES [dbo].[SYSTEM_USERS] (ID)

GO






